"""
Author : 张程东
File : check_info.py 
"""
import pandas as pd
import os
import re

def exis_position(x):
    """
    :param x: 输入的字段
    :return: 返回是否存在
    """
    regex = "院校代码"
    if x.find(regex) != -1 or x.find("录取院校") != -1:
        return True
    else:

        return False

def split_space(x):
    """
    :param x:字符串
    :return:没有空格的数据
    """
    return x.replace(" ","")

def read_all_data(path):
    """
    :param path: 某一年总数据的xlsx文件路径
    :return: 该年的总数据
    """
    #获取文件下的分表
    sheet_list=list(pd.read_excel(path,sheet_name=None))
    data=pd.DataFrame(columns=['student_name','student_id'])
    for i in sheet_list:
        student_part_data=pd.read_excel(path,sheet_name=i,dtype=object)
        if set(["姓名","身份证号"]).issubset(list(student_part_data)):
            student_part_data=student_part_data.loc[:,["姓名","身份证号"]]
            student_part_data.rename(columns={"姓名":"student_name","身份证号":"student_id"},inplace=True)
            data=pd.concat([data,student_part_data],ignore_index=True,axis=0)

    data['flag']="总表数据"
    data['student_name']=data['student_name'].astype(str).apply(split_space)
    data['student_id']=data['student_id'].astype(str).apply(split_space)
    return data

def get_ocr_data(path):
    """
    :param path: 分表文件路径
    :return: 处理后的分表数据
    """
    #读取文件信息
    ocr_data=pd.read_excel(path)
    column_name=list(ocr_data.columns)[0]
    flag=exis_position(column_name)
    if flag:
        ocr_data_info=pd.read_excel(path,skiprows=[0,1,2,3],dtype=object)
        if set(['姓名','身份 证号']).issubset(ocr_data_info.columns):
            ocr=ocr_data_info.loc[:,['姓名','身份 证号']]
            ocr.rename(columns={"姓名":"student_name","身份 证号":"student_id"},inplace=True)
            ocr.dropna(axis=0, inplace=True)
            ocr['flag'] = '分表数据'
            ocr['student_name']=ocr['student_name'].astype(str).apply(split_space)
            ocr['student_id']=ocr['student_id'].astype(str).apply(split_space)
            return ocr
        if set(['姓名','身份证号']).issubset(ocr_data_info.columns):
            ocr=ocr_data_info.loc[:,['姓名','身份证号']]
            ocr.rename(columns={"姓名":"student_name","身份证号":"student_id"},inplace=True)
            ocr.dropna(axis=0, inplace=True)
            ocr['flag'] = '分表数据'
            ocr['student_name']=ocr['student_name'].astype(str).apply(split_space)
            ocr['student_id']=ocr['student_id'].astype(str).apply(split_space)
            return ocr
        else:
            print(path)
            print("列出错")

    else:
        ocr_data_info=pd.read_excel(path,skipfooter=4,dtype=object)
        if set(['姓名', '身份 证号']).issubset(ocr_data_info):
            ocr = ocr_data_info.loc[:, ['姓名', '身份 证号']]
            ocr.rename(columns={"姓名": "student_name", "身份 证号": "student_id"}, inplace=True)
            ocr.dropna(axis=0,inplace=True)
            ocr['flag']='分表数据'
            ocr['student_name'] = ocr['student_name'].astype(str).apply(split_space)
            ocr['student_id'] = ocr['student_id'].astype(str).apply(split_space)
            return ocr
        else:
            print(path)
            print("列出错")




def check_data(full_data,check_data):
    """
    :param full_data: 总数据
    :param check_data: 分表数据
    :return: 不正确的数据
    """
    #获得分表中student_id
    check_data.dropna(axis=0,inplace=True)
    student_id_list=check_data['student_id'].tolist()
    #从总表中获取student_id
    # print(full_data['student_id'].isin(student_id_list).value_counts())
    get_full_data=full_data[full_data['student_id'].isin(student_id_list)]
    data=pd.concat([check_data,get_full_data],ignore_index=True,axis=0)
    data.drop_duplicates(subset=["student_name","student_id"],keep=False,inplace=True)
    return data

def save_error(path,data):
    if not data.empty:
        data = data.sort_values(by=['student_name', 'student_id'])
        data.to_excel(path, index=None)


def mkdir_path(path):
    """
    :param path: 文件夹的创建
    """
    if not os.path.exists(path):
        os.mkdir(path)


if __name__=="__main__":
    all_data=read_all_data(r"F:\图书馆\数据\成人数据\2017年.xls")
    ocr_data=get_ocr_data(r"F:\图书馆\数据\成人数据\分表\2017-JX13-0013\2017-JX13-0013-007.xlsx")
    error=check_data(all_data,ocr_data)
    save_error("./error.xlsx",error)


"""
F:\图书馆\数据\成人数据/2017-JX13-0008/2017-JX13-0008-008.xlsx
F:\图书馆\数据\成人数据/2017-JX13-0018/2017-JX13-0018-041.xlsx
"""
